﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
    public class ReportServiceImpl:BaseServiceImpl, IReportService
    {
        public IList getUserCardReport()
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT uc_chargeMode,isnull(sum(uc_kf),0) zj,uc_state,count(uc_id) sl,isnull(sum(joy.getCardTimes(uc_id)),0) times FROM joy.UserCard GROUP BY uc_chargeMode,uc_state ORDER BY uc_chargeMode,uc_state");
            return base.getList(query);
        }
        public IList getBookReport(string v)
        {
            MyQuery query = new MyQuery();
            string group = "";
            if (v == "5")
            {
                group = " lb_bookType ";//类型
            }
            else if (v == "4")
            {
                group = " lb_level ";//级别
            }
            else if (v == "3")
            {
                group = " lb_forAge ";//适读年龄
            }
            else if (v == "2")
            {
                group = " lb_aboutCd,mp3,eb ";//按图书附件（有CD图书、有音频图书、有音频电子书）
            }
            else if (v == "1")
            {
                group = " lb_ebook ";//图书状态
            }
            else
            {
                group = " lb_language ";//语种
            }
            query.setQueryString("SELECT " + group + ",isnull(sum(lb_price),0) zj,isnull(sum(lb_booksCount),0) sl,isnull(sum(joy.getBookBorrowCount(lb_id)),0) jscs,(isnull(sum(lb_booksCount),0)-isnull(sum(lb_inventory),0)) dqjcl,count(lb_id) lbidCount FROM (SELECT *,case when lb_aboutMp3 is null then 0 else 1 end mp3,case when lb_aboutEbook is null then 0 else 1 end eb FROM joy.LibraryBook) a Group by " + group);
            return base.getList(query);
        }
        public IList getUserReport(string v)
        {
            MyQuery query = new MyQuery();
            string group = "";
            if (v == "3")//按嘉沃级别(前四位为统计标示)
            {
                group = " jwcalss ";
            }
            else if (v == "2")
            {
                group = " u_state ";//学员状态
            }
            else if (v == "1")
            {
                group = " u_vip ";//会员状态
            }
            else
            {
                group = " u_state,u_vip ";//语种
            }
            query.setQueryString("SELECT  " + group + " ,count(u_id) zrs,isnull(sum(_jycs),0) jycs,isnull(sum(_jytssl),0) jytssl from (select (select isnull(sum(joy.getCardTimes(c_ucid)),0) from joy.UserCardCost where c_uid=u_id) _jycs,(select count(lbid_id) from  joy.LibraryBookIndent a inner join joy.LibraryBookIndentDetail b on a.lbi_id=b.lbid_lbiid where lbi_uid=u_id) _jytssl,u_id,left((select uic_class from joy.UserClass where uic_id = u_jwclass),4) jwcalss,u_state,u_vip  FROM  joy.UserInfo ) f Group by " + group);
            return base.getList(query);
        }

        public IList getUserRankingReport(string s, string e)
        {
            MyQuery query = new MyQuery();
            string _sql = "";

            if (!String.IsNullOrEmpty(s))
            {
                _sql += " and lbi_time > @s";
                query.addParas("@s", s);
            }
            if (!String.IsNullOrEmpty(e))
            {
                e = e + " 23:59:59";
                _sql += " and lbi_time < @e";
                query.addParas("@e", e);
            }
            query.setQueryString("select  top 200 (select count(lbid_id) from  joy.LibraryBookIndent a inner join joy.LibraryBookIndentDetail b on a.lbi_id=b.lbid_lbiid where lbi_uid=u_id "+_sql +") jyl,u_id,u_name,u_enname  FROM  joy.UserInfo order by jyl desc");
            return base.getList(query);
        }
        public IList getBookRankingReport(string s, string e)
        {
            MyQuery query = new MyQuery();
            string _sql = "";

            if (!String.IsNullOrEmpty(s))
            {
                _sql += " and lbi_time > @s";
                query.addParas("@s", s);
            }
            if (!String.IsNullOrEmpty(e))
            {
                e = e + " 23:59:59";
                _sql += " and lbi_time < @e";
                query.addParas("@e", e);
            }
            query.setQueryString("select top 200 lbd_isbn,lb_bookName,(select count(lbid_lbdid) from joy.LibraryBookIndent a inner join joy.LibraryBookIndentDetail b on a.lbi_id=b.lbid_lbiid where lbid_lbdid = lbd_id "+_sql+" ) jycs from joy.LibraryBook a inner join joy.LibraryBookDetail b on a.lb_id = b.lbd_lbid order by jycs desc");
            return base.getList(query);
        }
        public IList getBasicReport(string in_s, string in_e)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT count(u_id) as t1 FROM joy.Userinfo WHERE u_addTime>@in_s and u_addTime<@in_e;");//新增会员
            sb.Append("SELECT count(u_id) as t2 FROM joy.Userinfo WHERE u_vipUpdateTime>@in_s and u_vipUpdateTime<@in_e and u_vip = @vip;");//退订会员
            sb.Append("select count(lbi_id) as t3 from joy.LibraryBookIndent WHERE lbi_time>@in_s and lbi_time<@in_e;");//借阅人数
            sb.Append("select count(lbid_lbdid) as t4 from joy.LibraryBookIndent a inner join joy.LibraryBookIndentDetail b on a.lbi_id=b.lbid_lbiid WHERE lbi_time>@in_s and lbi_time<@in_e;");//借出书籍
            sb.Append("select count(lbid_lbdid) as t5 from joy.LibraryBookIndent a inner join joy.LibraryBookIndentDetail b on a.lbi_id=b.lbid_lbiid WHERE lbi_state=@lbistate and lbi_time>@in_s and lbi_time<@in_e;");//归还书籍
            sb.Append("select count(lb_id) as t6 from joy.LibraryBook where lb_addtime>@in_s and lb_addtime<@in_e;");//入库图书 种
            sb.Append("select count(lb_id) as t7 from joy.LibraryBook a inner join joy.LibraryBookDetail b on a.lb_id=b.lbd_lbid  where lb_addtime>@in_s and lb_addtime<@in_e;");//入库图书 本
            sb.Append("select count(lb_id) as t8 from joy.LibraryBook a inner join joy.LibraryBookDetail b on a.lb_id=b.lbd_lbid  where lb_ebook!=@ebook and lb_addtime>@in_s and lb_addtime<@in_e;");//入库电子书
            sb.Append("select isnull(sum(uc_kf),0) as t9 from joy.UserCard a inner join joy.UserCardCost b on a.uc_id = b.c_ucid where uc_chargeMode != @mode and c_addTime>@in_s and c_addTime<@in_e;");//新增卡费
            sb.Append("select isnull(sum(uc_yj),0) as t10 from joy.UserCard a inner join joy.UserCardCost b on a.uc_id = b.c_ucid where uc_chargeMode != @mode and c_addTime>@in_s and c_addTime<@in_e;");//新增押金
            sb.Append("select isnull(sum(uctf_fy),0) as t11 from joy.UserCard a inner join joy.UserCardTF b on a.uc_id = b.uctf_ucid where uctf_addTime>@in_s and uctf_addTime<@in_e;");//退还费用
            sb.Append("select isnull(sum(uctf_yj),0) as t12 from joy.UserCard a inner join joy.UserCardTF b on a.uc_id = b.uctf_ucid where uctf_addTime>@in_s and uctf_addTime<@in_e;");//退还押金
            sb.Append("select isnull(sum(ie_money),0) as t13 from joy.IncomeExpenses where ie_money>0 and ie_addTime>@in_s and ie_addTime<@in_e;");//其他款项

            query.addParas("@in_s", in_s);
            in_e = in_e + " 23:59:59";
            query.addParas("@in_e", in_e);
            query.addParas("@vip", Enums.uservip.已注销);
            query.addParas("@lbistate",Enums.lbistate.已归还);
            query.addParas("@ebook", Enums.bookebook.图书);
            query.addParas("@mode", Enums.cardstate.未使用);
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
    }
}
